import os
import io
import time
import boto3
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
import pandas as pd
import pymysql
import yaml
import xlwings as xw
import win32com.client as win32
import win32clipboard
import win32con
from PIL import Image
import logging
from logging.handlers import TimedRotatingFileHandler

# 配置日志记录
log_formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
log_file = "app.log"

# 创建一个带有时间轮转的日志处理器
my_handler = TimedRotatingFileHandler(log_file, when='midnight', interval=1, backupCount=7)
my_handler.setFormatter(log_formatter)
my_handler.setLevel(logging.DEBUG)

# 创建日志器
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)
logger.addHandler(my_handler)

# 读取YAML配置文件
with open('config1.yml', 'r', encoding='utf-8') as file:
    config = yaml.safe_load(file)

class ExcelFileHandler(FileSystemEventHandler):
    def __init__(self, config):
        self.config = config
        self.sql = config['sql']
        self.data_columns = config['data_columns']
        self.minio_client = boto3.client(
            's3',
            endpoint_url=config['minio']['endpoint'],
            aws_access_key_id=config['minio']['access_key'],
            aws_secret_access_key=config['minio']['secret_key']
        )
        self.db_connection = pymysql.connect(
            host=config['database']['host'],
            port=config['database']['port'],
            user=config['database']['user'],
            password=config['database']['password'],
            database=config['database']['db'],
            connect_timeout=5  # 设置连接超时时间为5秒
        )
        self.XLS_DIR = config['Excel']['path']
        self.MINIO_ENDPOINT = config['minio']['endpoint']
        self.MINIO_ACCESS_KEY = config['minio']['access_key']
        self.MINIO_SECRET_KEY = config['minio']['secret_key']
        self.BUCKET_NAME = config['minio']['bucket']
        self.select_work_order_id_sql = config['sql']['select_target']
        self.tenant_id = config['tenant_id']
        self.line_id = config['line_id']
        self.processed_files = set()

    def on_created(self, event):
        if not event.is_directory and event.src_path.endswith('.xls'):
            logger.info(f"检测到新创建的Excel文件: {event.src_path}")
            time.sleep(0.5)
            self.read_excel_file(event.src_path)

    def query_work_order_id(self, cursor, select_fields):
        """ 查询 work_order_id """
        select_sql = self.select_work_order_id_sql.format(select_fields=', '.join(select_fields))
        logger.debug(select_sql)
        cursor.execute(select_sql)
        result = cursor.fetchone()
        if result:
            return result[0]
        return None

    def read_excel_file(self, file_path):
        try:
            # 使用xlwings打开Excel文件
            app = xw.App(visible=False)
            wb = app.books.open(file_path)
            sheet = wb.sheets[0]  # 假设我们只处理第一个工作表
            # 读取Excel文件的第一个工作表，跳过指定行数
            data = pd.read_excel(file_path, sheet_name=int(self.config['Excel']['sheet_name']),
                                 skiprows=int(self.config['Excel']['skippers']), header=None)
            # 获取列名与索引的映射
            column_names = list(data.columns)
            column_index_map = {col: idx for idx, col in enumerate(column_names)}
            # 确定需要的列索引
            required_columns_indices = self.config['data_columns']['columns']
            excel_basename = os.path.splitext(os.path.basename(file_path))[0] # 获取Excel文件的基本名字（不包括扩展名）
            logger.info(f"文件名称为{excel_basename}")
            # 使用win32com.client来处理形状
            excel = win32.Dispatch("Excel.Application")
            workbook = excel.Workbooks.Open(file_path)
            worksheet = workbook.Sheets(1)  # 假设我们只处理第一个工作表

            for shape in worksheet.Shapes:
                if shape.Type == 13:  # msoPicture 对应的类型值为 13
                    img_filename = f"{shape.Name}.jpg"  # 添加文件名作为图片名称的前缀
                    object_name = f"SPI/{os.path.basename(file_path)}_{img_filename}"
                    # 将图片复制到剪贴板
                    shape.Copy()
                    # 从剪贴板获取图片数据
                    win32clipboard.OpenClipboard()
                    clipboard_data = win32clipboard.GetClipboardData(win32con.CF_DIB)  # 使用CF_DIB以设备无关位图格式获取
                    win32clipboard.CloseClipboard()
                    # 使用PIL将DIB数据转换为JPEG
                    image_stream = io.BytesIO(clipboard_data)
                    with Image.open(image_stream) as img:
                        output_stream = io.BytesIO()
                        img.save(output_stream, format="JPEG")
                        image_data = output_stream.getvalue()
                    # 上传到MinIO
                    try:
                        self.minio_client.put_object(Bucket=self.BUCKET_NAME, Key=object_name, Body=image_data)
                        logger.info(f"图片已上传至MinIO: {self.BUCKET_NAME}/{object_name}")
                        # 获取图片的URL
                        url = self.minio_client.generate_presigned_url(
                            'get_object',
                            Params={'Bucket': self.BUCKET_NAME, 'Key': object_name},
                            ExpiresIn=3600  # URL的有效期为1小时
                        )
                        logger.info(f"url为{url}")
                        row_index = int(shape.TopLeftCell.Row) - 2
                        if 0 <= row_index < len(data):
                            row_dict = data.iloc[row_index].to_dict()
                            filtered_row_dict = {}
                            for index in required_columns_indices:
                                if index < len(column_names):
                                    col_name = column_names[index]
                                    if col_name in row_dict:
                                        filtered_row_dict[str(index)] = row_dict[col_name]
                            filtered_row_dict['13'] = url
                            self.insert_into_database(filtered_row_dict, [str(idx) for idx in filtered_row_dict])
                    except Exception as e:
                        logger.error(f"上传或获取图片URL时发生错误: {e}")
                        import traceback
                        traceback.print_exc()
            wb.close()
            app.quit()
            workbook.Close(SaveChanges=False)
            excel.Quit()

        except Exception as e:
            logger.error(f"无法处理文件: {e}")
            import traceback
            traceback.print_exc()

    def insert_into_database(self, data, data_columns):
        cursor = None
        try:
            # 检查数据库连接是否有效
            self.db_connection.ping(reconnect=True)
            cursor = self.db_connection.cursor()
            select_fields = self.config['select_fields']
            work_order_id = self.query_work_order_id(cursor, select_fields)
            if not work_order_id:
                logger.warning("无法获取 work_order_id")
                return
            target_fields = self.config['target_fields']
            values = [data[col] for col in data_columns]
            values.append(work_order_id)
            placeholders = ', '.join(['%s'] * len(values))
            insert_sql = self.config['sql']['insert_url']
            sql = insert_sql.format(target_fields=', '.join(target_fields), placeholders=placeholders)
            logger.info("开始执行数据库插入操作")
            cursor.execute(sql, values)
            self.db_connection.commit()
            logger.info("数据已成功插入数据库")
        except pymysql.OperationalError as e:
            logger.error(f"数据库操作失败: {e}")
            # 如果是连接相关的错误，尝试重新连接
            if e.args[0] in (2006, 2013, 2014, 2045, 2055):  # 常见的连接错误码
                logger.warning("尝试重新连接数据库...")
                self.reconnect_to_database()
                # 重新执行插入操作
                self.insert_into_database(data, data_columns)
        except Exception as e:
            self.db_connection.rollback()
            logger.error(f"数据库插入失败: {e}")
            import traceback
            traceback.print_exc()
        finally:
            if cursor:
                cursor.close()

    def reconnect_to_database(self):
        try:
            self.db_connection.close()
            self.db_connection = pymysql.connect(
                host=self.config['database']['host'],
                port=self.config['database']['port'],
                user=self.config['database']['user'],
                password=self.config['database']['password'],
                database=self.config['database']['db'],
                connect_timeout=5  # 设置连接超时时间为5秒
            )
            logger.info("数据库重新连接成功")
        except Exception as e:
            logger.error(f"数据库重新连接失败: {e}")
            import traceback
            traceback.print_exc()

if __name__ == "__main__":
    path = config['Excel']['path']
    event_handler = ExcelFileHandler(config)
    observer = Observer()
    observer.schedule(event_handler, path, recursive=True)  # recursive=True 也观察子目录
    observer.start()
    logger.info(f'Starting to monitor {config["Excel"]["path"]}...')
    try:
        while True:
            time.sleep(1)
    except KeyboardInterrupt:
        observer.stop()
    observer.join()